package databaseController;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Car;
import model.CarModel;
import model.Driver;
import model.DriverLicense;
import model.Fine;
import model.Ipva;
import model.LicensePlate;
import model.Occurrence;
import model.Sensor;

public class DatabaseController {
	
	private Connection connection;
	private static DatabaseController instance;
	
	private DatabaseController() {
		try {
			Class.forName("org.sqlite.JDBC");
			this.connection = DriverManager.getConnection("jdbc:sqlite:test.db");
		    Statement stat = this.connection.createStatement();
		    //stat.executeUpdate("drop table if exists driver;");
		    stat.executeUpdate("create table if not exists driver (" +
						    		"driver_id integer, " +
						    		"driver_license_register text, " +
						    		"driver_license_expiration_date text, " +
						    		"driver_license_uf text, " +
						    		"driver_name text, " +
						    		"driver_address text, " +
						    		"constraint pk_driver primary key(driver_id));");
		    //stat.executeUpdate("create index ind_driver_license_register on driver (driver_license_register);");
		    
		    //stat.executeUpdate("drop table if exists car_model;");
		    stat.executeUpdate("create table if not exists car_model (" +
						    		"car_model_id integer, " +
						    		"car_model_manufactorer text, " +
						    		"car_model_name text, " +
						    		"car_model_year text," +
						    		"constraint pk_car_model primary key(car_model_id));");
		    
		    //stat.executeUpdate("drop table if exists car;");
		    stat.executeUpdate("create table if not exists car (" +
						    		"car_id integer, " +
						    		"license_plate_register text not null, " +
						    		"license_plate_uf text not null, " +
						    		"license_plate_city text not null, " +
						    		"car_image text, " +
						    		"car_stolen text, " +
						    		"driver_id integer null, " +
						    		"car_model_id integer not null, " +
						    		"constraint pk_car primary key(car_id), " +
						    		"foreign key (driver_id) references driver(id), " +
						    		"foreign key (car_model_id) references car_model(id));");
		    //stat.executeUpdate("create index ind_license_plate on car (license_plate_register);");
		    
		    //stat.executeUpdate("drop table if exists fine;");
		    stat.executeUpdate("create table if not exists fine (" +
		    						"fine_id integer, " +
						    		"car_id integer, " +
						    		"fine_value real, " +
						    		"fine_date text, " +
						    		"fine_payed integer," +
						    		"fine_reason text," +
						    		"constraint pk_fine primary key(fine_id));");
		    
		    //stat.executeUpdate("drop table if exists ipva;");
		    stat.executeUpdate("create table if not exists ipva (" +
		    						"ipva_id integer, " +
						    		"car_id integer, " +
						    		"ipva_value real, " +
						    		"ipva_date text, " +
						    		"ipva_payed integer," +
						    		"constraint pk_fine primary key(ipva_id));");
		    
		    //stat.executeUpdate("drop table if exists sensor;");
		    stat.executeUpdate("create table if not exists sensor (" +
		    						"sensor_id integer, " +
						    		"sensor_code text, " +
						    		"sensor_place text, " +
						    		"constraint pk_sensor primary key(sensor_id));");
		    //stat.executeUpdate("create index ind_sensor on sensor (sensor_id);");
		    
		    //stat.executeUpdate("drop table if exists occurrence;");
		    stat.executeUpdate("create table if not exists occurrence (" +
		    						"occurrence_id integer, " +
						    		"car_id integer, " +
						    		"sensor_id integer, " +
						    		"occurrence_date text, " +
						    		"occurrence_place text, " +
						    		"constraint pk_occurrence primary key(occurrence_id));");		    
		}
		catch (SQLException e) {
			System.err.println("DatabaseController:: Couldn't create connection " + e);
			System.exit(1);
		} catch (ClassNotFoundException e) {
			System.err.println(e);
		}
	}
	
	public static DatabaseController getInstance() {
		return instance;
	}
	
	public static void createInstance() {
		if (instance == null) {
			instance = new DatabaseController();
		}
	}
	
	public synchronized void addOccurrence(String sensorCode, String licensePlate) {
		try {			
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select sensor_id, sensor_place from sensor where sensor.sensor_code = \"" + sensorCode +"\";");
		    
		    Integer sensorId = null;
		    String sensorPlace = null;
		    while (rs.next()) {
		    	sensorId = rs.getInt("sensor_id");
		    	sensorPlace = rs.getString("sensor_place");
		    }
		    rs.close();				
			
		    rs = stat.executeQuery("select car_id from car where license_plate_register = \"" + licensePlate +"\";");
		    
		    Integer carId = null;
		    while (rs.next()) {
		    	carId = rs.getInt("car_id");
		    }
		    rs.close();				
			
			PreparedStatement prep = this.connection.prepareStatement("insert into occurrence values(null, ?, ?, ?, ?);");
			prep.setInt(1, carId);			
			prep.setInt(2, sensorId);
			prep.setString(3, "");
			prep.setString(4, sensorPlace);
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);
		}
		catch (SQLException e) {
			System.err.println(e);
		}		
	}
	
	public synchronized void addSensor(Sensor sensor) {	
		try {
			PreparedStatement prep = this.connection.prepareStatement("insert into sensor values(null, ?, ?);");
			prep.setString(1, sensor.getCode());
			prep.setString(2, sensor.getPlace());
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);
		}
		catch (SQLException e) {
			System.err.println(e);
		}
	}	
	
	public synchronized void addFine(Fine fine) {	
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select car_id from car where license_plate_register = \"" + fine.getCar().getLicensePlate().getNumber() +"\";");
		    
		    Integer carId = null;
		    while (rs.next()) {
		    	carId = rs.getInt("car_id");
		    }
		    rs.close();			
			
			PreparedStatement prep = this.connection.prepareStatement("insert into fine values(null, ?, ?, ?, ?, ?);");
			prep.setInt(1, carId);
			prep.setFloat(2, fine.getValue());
			prep.setString(3, fine.getDate());
			prep.setBoolean(4, fine.isPayed());
			prep.setString(5, fine.getReason());
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);
		}
		catch (SQLException e) {
			System.err.println(e);
		}
	}	
	

	public synchronized void addIpva(Ipva ipva) {
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select car_id from car where license_plate_register = \"" + ipva.getCar().getLicensePlate().getNumber() +"\";");
		    
		    Integer carId = null;
		    while (rs.next()) {
		    	carId = rs.getInt("car_id");
		    }
		    rs.close();			
			
			PreparedStatement prep = this.connection.prepareStatement("insert into ipva values(null, ?, ?, ?, ?);");
			prep.setInt(1, carId);
			prep.setFloat(2, ipva.getValue());
			prep.setString(3, ipva.getDate());
			prep.setBoolean(4, ipva.isPayed());
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);
		}
		catch (SQLException e) {
			System.err.println(e);
		}	
	}
	
	public synchronized void addDriver(Driver driver) {		
		try {
			PreparedStatement prep = this.connection.prepareStatement("insert into driver values(null, ?, ?, ?, ?, ?);");
			prep.setString(1, driver.getDriverLicence().getRegister());
			prep.setString(2, driver.getDriverLicence().getExpitationDate());
			prep.setString(3, driver.getDriverLicence().getUf());
			prep.setString(4, driver.getName());			
			prep.setString(5, driver.getAddress());
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);
		}
		catch (SQLException e) {
			System.err.println(e);
		}
	}
	
	public synchronized void addCar(Car car) {
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select driver_id from driver where driver_license_register = \"" + car.getDriver().getDriverLicence().getRegister() +"\";");
		    
		    Integer driverId = null;
		    while (rs.next()) {
		    	driverId = rs.getInt("driver_id");
		    }
		    rs.close();
		    
		    rs = stat.executeQuery("select car_model_id from car_model where car_model_manufactorer = \"" + car.getCarModel().getManufactorer() + "\" and " +
		    																"car_model_year = \"" + car.getCarModel().getYear() + "\" and " +
		    																"car_model_name = \"" + car.getCarModel().getName() + "\";");
		    Integer carModelId = null;
		    while (rs.next()) {
		    	carModelId = rs.getInt("car_model_id");
		    }
		    rs.close();
		    
			PreparedStatement prep = this.connection.prepareStatement("insert into car values(null, ?, ?, ?, ?, ?, ?, ?);");
			prep.setString(1, car.getLicensePlate().getNumber());
			prep.setString(2, car.getLicensePlate().getUf());
			prep.setString(3, car.getLicensePlate().getCity());
			prep.setString(4, car.getImage());
			prep.setBoolean(5, car.isStolen());
			prep.setInt(6, driverId);
			prep.setInt(7, carModelId);
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);
		    rs.close();		
		}
		catch (SQLException e) {
			System.err.println(e);
		}	
	}
	
	public synchronized void addCarModel(CarModel carModel) {		
		try {
			PreparedStatement prep = this.connection.prepareStatement("insert into car_model values(null, ?, ?, ?);");
			prep.setString(1, carModel.getManufactorer());
			prep.setString(2, carModel.getName());
			prep.setString(3, carModel.getYear());
			prep.addBatch();			
			this.connection.setAutoCommit(false);
			prep.executeBatch();
			this.connection.commit();
			this.connection.setAutoCommit(true);			
		}
		catch (SQLException e) {
			System.err.println(e);
		}
	}
		
	public synchronized Car getCarByLicensePlate(String licensePlate) {
		Car car = null;
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from car CROSS JOIN driver CROSS JOIN car_model where license_plate_register = \"" + licensePlate +"\"" +
		    									" and driver.driver_id = car.car_id and car_model.car_model_id = car.car_model_id;");
		    while (rs.next()) {
		    	DriverLicense driverLicense = new DriverLicense(rs.getString("driver_license_register"), rs.getString("driver_license_expiration_date"), rs.getString("driver_license_uf"));
		    	Driver driver = new Driver(driverLicense, rs.getString("driver_name"), rs.getString("driver_address"));
		    	
		    	CarModel carModel = new CarModel(rs.getString("car_model_name"), rs.getString("car_model_year"), rs.getString("car_model_manufactorer"));
		    	
		    	car = new Car(new LicensePlate("", rs.getString("license_plate_register"), rs.getString("license_plate_city"), rs.getString("license_plate_uf")), carModel, driver, rs.getString("car_image"), rs.getBoolean("car_stolen"));
		    }
		    rs.close();
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return car;
	}

	public synchronized ArrayList<Fine> getFineByLicensePlate(String licensePlate) {
		ArrayList<Fine> collection = new ArrayList<Fine>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from car CROSS JOIN fine where license_plate_register = \"" + licensePlate +"\"" +
		    									" and car.car_id = fine.car_id;");
		    while (rs.next()) {
		    	collection.add(new Fine(null, rs.getFloat("fine_value"), rs.getString("fine_date"), rs.getBoolean("fine_payed"), rs.getString("fine_reason")));
		    }
		    rs.close();
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}	
	
	public synchronized ArrayList<Ipva> getIpvaByLicensePlate(String licensePlate) {
		ArrayList<Ipva> collection = new ArrayList<Ipva>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from car CROSS JOIN ipva where license_plate_register = \"" + licensePlate +"\"" +
		    									" and car.car_id = ipva.car_id;");
		    while (rs.next()) {
		    	collection.add(new Ipva(null, rs.getFloat("ipva_value"), rs.getString("ipva_date"), rs.getBoolean("ipva_payed")));
		    }
		    rs.close();
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}	
	
	public synchronized ArrayList<Occurrence> getOccurrencesDatabase(String licensePlate, int limit) {
		ArrayList<Occurrence> collection = new ArrayList<Occurrence>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from occurrence CROSS JOIN sensor CROSS JOIN car where occurrence.sensor_id = sensor.sensor_id and occurrence.car_id = car.car_id and car.license_plate_register = \"" + licensePlate + "\" order by occurrence.occurrence_date desc limit " + limit + ";");
		    while (rs.next()) {		    	
		    	Occurrence occurrence = new Occurrence(new Sensor(rs.getString("sensor_code"), rs.getString("sensor_place")), new Car(new LicensePlate("", rs.getString("license_plate_register"), rs.getString("license_plate_city"), rs.getString("license_plate_uf")), null, null, rs.getString("car_image"), rs.getBoolean("car_stolen")), rs.getString("occurrence_date"), rs.getString("occurrence_place"));
		    	collection.add(occurrence);
		    }
		    rs.close();			
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}	
	
	public synchronized ArrayList<Occurrence> getOccurrencesDatabase(String licensePlate) {
		ArrayList<Occurrence> collection = new ArrayList<Occurrence>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from occurrence CROSS JOIN sensor CROSS JOIN car where occurrence.sensor_id = sensor.sensor_id and occurrence.car_id = car.car_id and car.license_plate = \"" + licensePlate + "\";");
		    while (rs.next()) {		    	
		    	Occurrence occurrence = new Occurrence(new Sensor(rs.getString("sensor_code"), rs.getString("sensor_place")), new Car(new LicensePlate("", rs.getString("license_plate_register"), rs.getString("license_plate_city"), rs.getString("license_plate_uf")), null, null, rs.getString("car_image"), rs.getBoolean("car_stolen")), rs.getString("occurrence_date"), rs.getString("occurrence_place"));
		    	collection.add(occurrence);
		    }
		    rs.close();			
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}	
	
	public synchronized ArrayList<Driver> getDriversDatabase() {
		ArrayList<Driver> collection = new ArrayList<Driver>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from driver;");
		    while (rs.next()) {
		    	DriverLicense driverLicense = new DriverLicense(rs.getString("driver_license_register"), rs.getString("driver_license_expiration_date"), rs.getString("driver_license_uf"));
		    	Driver driver = new Driver(driverLicense, rs.getString("driver_name"), rs.getString("driver_address"));
		    	
		    	collection.add(driver);
		    }
		    rs.close();			
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}

	public synchronized ArrayList<Car> getCarsDatabase() {
		ArrayList<Car> collection = new ArrayList<Car>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from car CROSS JOIN driver CROSS JOIN car_model where driver.driver_id = car.car_id and car_model.car_model_id = car.car_model_id;");
		    while (rs.next()) {
		    	DriverLicense driverLicense = new DriverLicense(rs.getString("driver_license_register"), rs.getString("driver_license_expiration_date"), rs.getString("driver_license_uf"));
		    	Driver driver = new Driver(driverLicense, rs.getString("driver_name"), rs.getString("driver_address"));
		    	
		    	CarModel carModel = new CarModel(rs.getString("car_model_name"), rs.getString("car_model_year"), rs.getString("car_model_manufactorer"));
		    	
		    	collection.add(new Car(new LicensePlate("", rs.getString("license_plate_register"), rs.getString("license_plate_city"), rs.getString("license_plate_uf")), carModel, driver, rs.getString("car_image"), rs.getBoolean("car_stolen")));
		    }
		    rs.close();
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}

	public synchronized ArrayList<CarModel> getCarModelsDatabase() {
		ArrayList<CarModel> collection = new ArrayList<CarModel>();
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select * from car_model;");
		    while (rs.next()) {
	    		CarModel carModel = new CarModel(rs.getString("car_model_name"), rs.getString("car_model_year"), rs.getString("car_model_manufactorer"));
		    	
		    	collection.add(carModel);
		    }
		    rs.close();			
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		
		return collection;
	}

	public synchronized boolean hasSensor(String user) {
		boolean found = false;
		
		try {
			Statement stat = this.connection.createStatement();
		    ResultSet rs = stat.executeQuery("select sensor_code from sensor where sensor_code = \"" + user +"\";");
		    
		    if (rs.next()) {
		    	found = true;
		    }
		    rs.close();
		}
		catch (SQLException e) {
			System.err.println(e);
		}
		return found;
	}
}
